﻿Namespace Sistem
	Module Functions

		Sub KimlikDuzelt(Token As Token, sno As Integer, yenikimlik As String)
			Dim Rs As Recordset = Token.OpenSQL("select o.name from syscolumns c inner join sysobjects o on c.id = o.id where c.name = 'kimlik' and o.type = 'U' and exists(select * from syscolumns where id = o.id and name = 'sno') order by c.name")
			Token.BeginTran()
			Do Until Rs.EOF
				Token.ExecSQL("update " & Rs(0) & " set kimlik = @kimlik where sno = @sno", yenikimlik, sno)
				Rs.MoveNext()
			Loop
			Token.CommitTran()
		End Sub

		Sub SigortaDuzelt(Token As Token, sno As Integer, yenisno As Integer, exists As Boolean)
			Dim Rs As Recordset = Token.OpenSQL("select o.name from syscolumns c inner join sysobjects o on c.id = o.id where c.name = 'sno' and o.type = 'U' order by c.name")
			Token.BeginTran()
			Do Until Rs.EOF
				If exists And Rs(0) = "sgenel" Then
					Token.ExecSQL("delete from sgenel where sno = @sno", sno)
				Else
					Token.ExecSQL("update " & Rs(0) & " set sno = @yenisno where sno = @sno", yenisno, sno)
				End If
				Rs.MoveNext()
			Loop
			Token.CommitTran()
		End Sub

		Sub PasaportDuzelt(Token As Token, sno As Integer, yenipasaport As String)
			Dim Rs As Recordset = Token.OpenSQL("select o.name from syscolumns c inner join sysobjects o on c.id = o.id where c.name = 'pasaport' and o.type = 'U' and exists(select * from syscolumns where id = o.id and name = 'sno') order by c.name")
			Token.BeginTran()
			Do Until Rs.EOF
				Token.ExecSQL("update " & Rs(0) & " set pasaport = @pasaport where sno = @sno", yenipasaport, sno)
				Rs.MoveNext()
			Loop
			Token.CommitTran()
		End Sub

		Function Tablolar(Token As Token, db As String) As Recordset
			If db <> "" Then db = db & ".dbo."
			Q("select name as Kod, name as Ad from " & db & "sysobjects")
			Q("where id in (select id from " & db & "syscolumns where name = 'rowid')")
			Q("and name not in ('_replication','_changes') or (name = 'emanet')")
			Q("order by name")
			Return Token.OpenSQL("*")
		End Function

		Function Alanlar(Token As Token, db As String, table As String) As Recordset
			If db <> "" Then db = db & ".dbo."
			Dim id = Token.OpenSQL("select id from " & db & "sysobjects where name = @name", table)(0)
			Return Token.OpenSQL("select name, '=' as dot from " & db & "syscolumns where id = @id order by colid", id)
		End Function

		Sub GenelDuzelt(Token As Token, AllChanges As String)
			Token.BeginTran()
			Token.ExecSQL(AllChanges)
			Token.CommitTran()
		End Sub

		Function Kodlar(Token As Token) As Recordset
			Return Token.OpenSQL("select * from syskod2 order by kod")
		End Function

		Function FaaliyetKodlari(Token As Token) As Recordset
			Return Token.OpenSQL("select * from faaliyetkod order by kod")
		End Function

		Function IhtiyatSandigiOku(Token As Token, Tarih As Date) As Recordset
			Q("select right('000000000'+LTRIM(b.sno),9)+left(ltrim(RTRIM(b.kimlik))+REPLICATE(' ',11),10)+b.cins+left(RTRIM(b.adi)+' '+RTRIM(b.soyadi)+REPLICATE(' ',50),50)+")
			Q("c.sicilno+left(RTRIM(c.isyeriadi)+REPLICATE(' ',50),50)+'KKTC'+CONVERT(char(6),a.bordrotar,112)+right('00'+LTRIM(pogs),2)+'YYYYMMDD'+CONVERT(char(8),a.islemtar,112)+")
			Q("right(REPLICATE('0',11)+ltrim(convert(int,a.pek*100)),11)+")
			Q("right(REPLICATE('0',11)+ltrim(convert(int,a.pek*case a.bordro when 'D' then 8 when 'D1' then 9 when 'D2' then 8.25 when 'E' then 7 when 'E1' then 8.25 else 0 end)),11)+")
			Q("right(REPLICATE('0',11)+ltrim(convert(int,a.pek*(c.tehlike/2+case a.bordro when 'D' then 9.5 when 'D1' then 10.5 when 'D2' then 9.75 when 'E' then 11.5 when 'E1' then 13.75 when 'C' then 4.5 else 0 end))),11)+")
			Q("right(REPLICATE('0',4)+ltrim(convert(int,100*case a.bordro when 'D' then 8 when 'D1' then 9 when 'D2' then 8.25 when 'E' then 7 when 'E1' then 8.25 else 0 end)),4)+")
			Q("right(REPLICATE('0',4)+ltrim(convert(int,100*(c.tehlike/2+case a.bordro when 'D' then 9.5 when 'D1' then 10.5 when 'D2' then 9.75 when 'E' then 11.5 when 'E1' then 13.75 when 'C' then 4.5 else 0 end))),4)+")
			Q("case when a.bordro in ('E','E1') then 'E' else 'H' end+case when d.geczam>0 then 'E' else 'H' end as text")
			Q("from shesk a inner join sgenel b on a.sno = b.sno and a.repsube = b.repsube inner join isyerigenel c on a.isyerino = c.isyerino left join pgenel d on a.repsube = d.repsube and a.makbuzno = d.makbuzno")
			Q("where a.bordrotar = :tarih and b.uyr='01.03.001' and a.bordro in ('D','D1','D2','E','E1','C') and a.pogs<>0")
			Return Token.OpenSQL("*", Tarih)
		End Function

		Function ImzaSource(Yetki As String) As String
			Return "q@select rowid as kod, adi + ' (' + unvan + ')' as ad from imza where yetki = '" & Yetki & "'"
		End Function

		Function Imza(Token As Token, kod As Integer) As Recordset
			Return Token.OpenSQL("select * from imza where rowid = @kod", kod)
		End Function

	End Module
End Namespace
